package com.tsmti.core.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;

/**
 * @Package：com.tsmti.core.util
 * @Class：ExportExcelUtil
 * @Description： Excel导出工具类
 * @Author：yuzh
 * @Date：Created in 2018/9/6 17:47
 * @Company:
 * @Version：
 * @Modified By:
 */
public class ExportExcelUtil {

    /**
     * 显示的导出表的标题
     */
    private String title;
    /**
     * 导出表的列名
     */
    private String[] rowName ;

    /**
     * 多表头列导出
     */
    private String[][] rowNames ;
    /**
     * 导出表的列名对应的列标签
     */
    private String[] label ;

    private JSONArray dataList = new JSONArray();
    /**
     * 导出是否显示行号
     */
    private Boolean isRownum = false;

    /**
     * 构造方法，传入要导出的数据
     * @param title 导出标题
     * @param rowName 列标题
     * @param dataList 导出数据
     */
    public ExportExcelUtil(String title,String[] rowName, String[] label, JSONArray dataList){
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
        this.label = label;
    }

    /**
     * 构造方法，传入要导出的数据
     * @param title 导出标题
     * @param rowNames 列标题
     * @param dataList 导出数据
     */
    public ExportExcelUtil(String title, String[][] rowNames, String[] label, JSONArray dataList, Boolean isRownum){
        this.dataList = dataList;
        this.rowNames = rowNames;
        this.title = title;
        this.label = label;
        this.isRownum = isRownum;
    }

    /**
     * 导出数据
     * @param response http response
     */
    public void export(HttpServletResponse response) throws Exception{
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(title);

            /*产生表格标题行*/
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);

            /*sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】*/
            /*获取列头样式对象*/
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
            /*单元格样式对象*/
            HSSFCellStyle style = this.getStyle(workbook);

            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);

            /*定义所需列数*/
            int columnNum = rowName.length;
            /*在索引2的位置创建行(最顶端的行开始的第二行)*/
            HSSFRow rowRowName = sheet.createRow(2);

            /*将列头设置到sheet的单元格中*/
            for(int n=0;n<columnNum;n++){
                /*创建列头对应个数的单元格*/
                HSSFCell  cellRowName = rowRowName.createCell(n);
                /*设置列头单元格的数据类型*/
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                /*设置列头单元格的值*/
                cellRowName.setCellValue(text);
                /*设置列头单元格样式*/
                cellRowName.setCellStyle(columnTopStyle);
            }

            /*将查询出的数据设置到sheet对应的单元格中*/
            for(int i = 0; i < dataList.size(); i++){
                JSONObject obj = dataList.getJSONObject(i);
                HSSFRow row = sheet.createRow(i+3);

                for(int j = 0; j < label.length; j++){
                    HSSFCell cell;
                    if(j == 0){
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(i+1);
                    }else{
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                        if(!"".equals(label[j])){
                            cell.setCellValue(obj.getString(label[j]));
                        }
                    }
                    cell.setCellStyle(style);
                }
            }
            exportColumnNum(columnNum,sheet,workbook,response);

        } catch(Exception e){
            e.printStackTrace();
        }

    }

    /**
     * 列头单元格样式
     * @param workbook 工作簿样式
     */
    private HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        /*设置字体*/
        HSSFFont font = workbook.createFont();
        /*设置字体大小*/
        font.setFontHeightInPoints((short)11);
        /*字体加粗*/
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        /*设置字体名字*/
        font.setFontName("Courier New");
        /*设置样式;*/
        HSSFCellStyle style = workbook.createCellStyle();
        /*设置底边框;*/
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        /*设置底边框颜色;*/
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        /*设置左边框;*/
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        /*设置左边框颜色;*/
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        /*设置右边框;*/
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        /*设置右边框颜色;*/
        style.setRightBorderColor(HSSFColor.BLACK.index);
        /*设置顶边框;*/
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        /*设置顶边框颜色;*/
        style.setTopBorderColor(HSSFColor.BLACK.index);
        /*在样式用应用设置的字体;*/
        style.setFont(font);
        /*设置自动换行;*/
        style.setWrapText(false);
        /*设置水平对齐的样式为居中对齐;*/
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        /*设置垂直对齐的样式为居中对齐;*/
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }

    /**
     * 列数据信息单元格样式
     * @param workbook 单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        /*设置字体*/
        HSSFFont font = workbook.createFont();
        /*设置字体大小*/
        font.setFontHeightInPoints((short)10);
        /*字体加粗*/
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        /*设置字体名字*/
        font.setFontName("Courier New");
        /*设置样式;*/
        HSSFCellStyle style = workbook.createCellStyle();
        /*设置底边框;*/
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        /*设置底边框颜色;*/
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        /*设置左边框;*/
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        /*设置左边框颜色;*/
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        /*设置右边框;*/
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        /*设置右边框颜色;*/
        style.setRightBorderColor(HSSFColor.BLACK.index);
        /*设置顶边框;*/
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        /*设置顶边框颜色;*/
        style.setTopBorderColor(HSSFColor.BLACK.index);
        /*在样式用应用设置的字体;*/
        style.setFont(font);
        /*设置自动换行;*/
        style.setWrapText(false);
        /*设置水平对齐的样式为居中对齐;*/
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        /*设置垂直对齐的样式为居中对齐;*/
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;

    }

    /**
     * 导出数据
     * @param response http response
     * @throws Exception 抛出异常
     */
    public void exportWithGroupHead(HttpServletResponse response) throws Exception{
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(title);

            /*产生表格标题行*/
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);

            /*sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】*/
            /*获取列头样式对象*/
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
            /*单元格样式对象*/
            HSSFCellStyle style = this.getStyle(workbook);

            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowNames[0].length-1)));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);

            /*定义所需列数*/
            int columnNum = rowNames[0].length;
            for(int i=0; i<rowNames.length; i++){
                /*在索引2的位置创建行(最顶端的行开始的第二行)*/
                HSSFRow rowRowName = sheet.createRow(2+i);

                /*将列头设置到sheet的单元格中*/
                for(int n=0;n<columnNum;n++){
                    /*创建列头对应个数的单元格*/
                    HSSFCell  cellRowName = rowRowName.createCell(n);
                    /*设置列头单元格的数据类型*/
                    cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                    HSSFRichTextString text = new HSSFRichTextString(rowNames[i][n]);
                    /*设置列头单元格的值*/
                    cellRowName.setCellValue(text);
                    /*设置列头单元格样式*/
                    cellRowName.setCellStyle(columnTopStyle);
                }
            }


            /*将查询出的数据设置到sheet对应的单元格中*/
            for(int i = 0; i < dataList.size(); i++){
                JSONObject obj = dataList.getJSONObject(i);
                HSSFRow row = sheet.createRow(i+2+rowNames.length);

                for(int j = 0; j < label.length; j++){
                    HSSFCell cell;
                    if(j == 0 && isRownum){
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(i+1);
                    }else{
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                        if(!"".equals(label[j])){
                            cell.setCellValue(obj.getString(label[j]));
                        }
                    }
                    cell.setCellStyle(style);
                }
            }
            exportColumnNum(columnNum,sheet,workbook,response);

        } catch(Exception e){
            e.printStackTrace();
        }

    }

    private void exportColumnNum(int columnNum,HSSFSheet sheet,HSSFWorkbook workbook,HttpServletResponse response){
        /*让列宽随着导出的列长自动适应*/
        for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                /*当前行未被使用过*/
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            if(colNum == 0){
                sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
            }else{
                sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
            }
        }

        if(workbook !=null){
            try {
                String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
                response.setContentType("APPLICATION/OCTET-STREAM");
                response.setHeader("Content-Disposition", "attachment; filename="
                        + new String(fileName.getBytes("gbk"),"iso-8859-1"));
                OutputStream out = response.getOutputStream();
                workbook.write(out);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}
